﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Collections

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Transaction
    Inherits System.Web.Services.WebService

    Dim lRetCode As Integer
    Dim lErrCode As Integer
    Dim sErrMsg As String
    <WebMethod()> _
    Public Function GetSAPXML(ByVal dbType As String, ByVal docEntry As String) As String
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        If dbType = PublicVariable.VSM Then
            oCompany = PublicVariable.mVSMDataComapny
        ElseIf dbType = PublicVariable.DEALER Then
            oCompany = PublicVariable.mDEADataComapny
        End If

        oCompany.XMLAsString = True
        'Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)
        'Dim oDocment As SAPbobsCOM.Payments = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments)
        'Dim oDocment As SAPbobsCOM.StockTransfer = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer)
        'Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes)
        'Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices)
        'Dim oDocment As SAPbobsCOM.Payments = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oVendorPayments)
        Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders)
        oDocment.GetByKey(docEntry)
        Dim s As String = oDocment.GetAsXML()
        Return s
    End Function

    <WebMethod()> _
    Public Function RemoveMarketingDocumentLine(ByVal strXml As String, ByVal DocType As String, ByVal Key As String, _
                                            ByVal IsUpdate As Boolean, ByVal dbType As String, ByVal dsRemove As DataSet, _
                                            ByVal ObjectType As String) As DataSet
        Dim b As New SAP_Functions
        'Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Try
            Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            Select Case DocType
                Case "30"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.JournalEntries)
                Case "97"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.SalesOpportunities)
                Case "191"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.ServiceCalls)
                Case "33"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Contacts)
                Case "221"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Attachments2)
                Case "2"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.BusinessPartners)
                Case "28"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.IJournalVouchers)
                Case Else
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)
            End Select

            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            Dim index = 0
            If IsUpdate Then
                oDocment = oCompany.GetBusinessObject(ObjectType)
                If oDocment.GetByKey(Key) Then
                    'Remove Line
                    If dsRemove IsNot Nothing Then
                        For Each row As DataRow In dsRemove.Tables(0).Rows
                            oDocment.Lines.SetCurrentLine(Integer.Parse(row("LineNum").ToString()) - index)
                            oDocment.Lines.Delete()
                            index += 1
                        Next
                        lErrCode = oDocment.Update()
                    End If
                Else
                    Return b.ReturnMessage(-1, "Record not found!")
                End If
            Else
                lErrCode = oDocment.Add()
            End If
            If lErrCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrMsg)
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                Dim lsDocEntry As String = "Operation successful!"
                If IsUpdate = False Then
                    lsDocEntry = oCompany.GetNewObjectKey()
                End If
                Return b.ReturnMessage(lErrCode, lsDocEntry)
            End If
        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetMarketingDocument_ReturnDS(ByVal DocType As String, ByVal DocEntry As String, ByVal dbType As String) As DataSet
        Try
            Dim sStr As String = ""

            Dim connect As New Connection()
            Dim HeaderTableName As String = ""
            Dim LineTableName1 As String = ""
            Dim LineTableName2 As String = ""
            Dim KeyName As String = "DocEntry"
            Dim strFilter As String = ""

            Select Case DocType
                '----------------------PURCHASE------------------------
                Case "18" 'AP INVOICE
                    HeaderTableName = "OPCH"
                    LineTableName1 = "PCH1"
                Case "19" 'AP CREDIT
                    HeaderTableName = "ORPC"
                    LineTableName1 = "RPC1"
                Case "20" 'GOODS RECEIPT PO
                    HeaderTableName = "OPDN"
                    LineTableName1 = "PDN1"
                Case "21" 'GOODS RETURN
                    HeaderTableName = "ORPD"
                    LineTableName1 = "RPD1"
                Case "22" 'PURCHASE ORDER
                    HeaderTableName = "OPOR"
                    LineTableName1 = "POR1"
                Case "540000006" 'PURCHASE QUOTATION"
                    HeaderTableName = "OPQT"
                    LineTableName1 = "PQT1"
                Case "1470000113" 'PURCHASE REQUEST"
                    HeaderTableName = "OPRQ"
                    LineTableName1 = "PRQ1"
                    '-------------------SALES--------------------------
                Case "13" 'AR INVOICE
                    HeaderTableName = "OINV"
                    LineTableName1 = "INV1"
                Case "14" 'AR CREDIT
                    HeaderTableName = "ORIN"
                    LineTableName1 = "RIN1"
                Case "15" 'DELIVERY
                    HeaderTableName = "ODLN"
                    LineTableName1 = "DLN1"
                    'LineTableName2 = "DLN3"
                Case "16" 'RETURN
                    HeaderTableName = "ORDN"
                    LineTableName1 = "RDN1"
                Case "17" 'SALES ORDER
                    HeaderTableName = "ORDR"
                    LineTableName1 = "RDR1"
                    'LineTableName2 = "RDR3"
                Case "23" 'SALES QUOTATION
                    HeaderTableName = "OQUT"
                    LineTableName1 = "QUT1"
                    LineTableName2 = "QUT3"
                    '---------------------INVENTORY----------------------
                Case "59" 'GOODS RECEIPT
                    HeaderTableName = "OIGN"
                    LineTableName1 = "IGN1"
                Case "60" 'GOODS ISSUE
                    HeaderTableName = "OIGE"
                    LineTableName1 = "IGE1"
                Case "67" 'TRANSFER
                    HeaderTableName = "OWTR"
                    LineTableName1 = "WTR1"
                Case "1250000001" 'TRANSFER REQUEST
                    HeaderTableName = "OWTQ"
                    LineTableName1 = "WTQ1"

                    '-------------------MASTER DATA------------------
                Case "171" 'EMPLOYEE
                    HeaderTableName = "OHEM"
                    LineTableName1 = "HEM1"
                    KeyName = "empID"
                Case "2" 'BUSINESS PARTNER
                    HeaderTableName = "OCRD"
                    LineTableName1 = "CRD1"
                    LineTableName2 = "OCPR"
                    KeyName = "cardcode"
                Case "4" 'ITEM
                    HeaderTableName = "OITM"
                    LineTableName1 = ""
                    KeyName = "itemcode"
                Case "11" 'CONTACT PERSON
                    HeaderTableName = "OCPR"
                    LineTableName1 = ""
                    KeyName = "cardcode"
                Case "12" 'USER
                    HeaderTableName = "OUSR"
                    LineTableName1 = "OUSR"
                    KeyName = "USERID"

                    '----------------------OTHERS--------------------
                Case "221" 'Attachment
                    HeaderTableName = "OATC"
                    LineTableName1 = "ATC1"
                    KeyName = "AbsEntry"
                Case "97" 'Sales opportunity
                    HeaderTableName = "OOPR"
                    LineTableName1 = "OPR1"
                Case "33" 'Activity
                    HeaderTableName = "OCLG"
                    LineTableName1 = ""
                    KeyName = "ClgCode"
                Case "30" 'Journal Entry
                    HeaderTableName = "OJDT"
                    LineTableName1 = "JDT1"
                    KeyName = "TransID"

                    '-------------------PRODUCTION--------------------
                Case "202" 'Production Order
                    HeaderTableName = "OWOR"
                    LineTableName1 = "WOR1"
                    'Case "60-202" 'ISSUE for production
                    '    HeaderTableName = "OIGE"
                    '    LineTableName1 = "IGE1"
                    '    strFilter = " And BaseType='202'"
                    'Case "59-202" 'RECEIPT from production
                    '    HeaderTableName = "OIGN"
                    '    LineTableName1 = "IGN1"
                    '    strFilter = " And BaseType='202"

                Case "198"
                    HeaderTableName = "OFCT"
                    LineTableName1 = "FCT1"
                    KeyName = "AbsID"
                    '--------------------PAYMENT----------------------
                Case "24" 'Incoming
                    HeaderTableName = "ORCT"
                    LineTableName1 = "RCT2"
                Case "46" 'Outgoing
                    HeaderTableName = "OVPM"
                    LineTableName1 = "VPM2"
            End Select
            'If DocEntry <= "0" Then
            '    Dim b As New SAP_Functions
            '    connect.setDB(UserID)
            '    DocEntry = b.GetMaxDocEntry(DocType, UserID, HeaderTableName, KeyName)
            'End If

            Dim ds As New DataSet("Document")
            Dim dt1 As New DataTable
            connect.setDB(dbType)
            Dim str As String
            str = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + HeaderTableName + " where " + KeyName + "='" + DocEntry + "' " + strFilter

            dt1 = connect.ObjectGetAll_Query(str, dbType).Tables(0)

            dt1.TableName = HeaderTableName
            ds.Tables.Add(dt1.Copy)

            '============== Hard code to get XML Object ===================================================================
            ''Dim b As New SAP_Functions
            ''Dim connect As New Connection()
            'Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
            'If dbType = PublicVariable.VSM Then
            '    oCompany = PublicVariable.mVSMDataComapny
            'ElseIf dbType = PublicVariable.DEALER Then
            '    oCompany = PublicVariable.mDEADataComapny
            'End If

            'oCompany.XMLAsString = True
            ''Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)
            ''Dim oDocment As SAPbobsCOM.Payments = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments)
            ''Dim oDocment As SAPbobsCOM.StockTransfer = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer)
            ''Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes)
            ''Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices)
            ''Dim oDocment As SAPbobsCOM.Payments = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oVendorPayments)
            'Dim oDocment As SAPbobsCOM.Documents = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations)
            'oDocment.GetByKey(DocEntry)
            'Dim s As String = oDocment.GetAsXML()
            '===============================================================================================================

            If LineTableName1 <> "" Then
                Dim dt2 As New DataTable
                connect.setDB(dbType)
                Dim str1 As String = ""

                Select Case DocType
                    Case "13", "67"
                        'str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, T1.SysNumber, T1.DistNumber, T1.InDate, T1.Notes, * from " + LineTableName1 + ", SRI1_LINK T0 left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + "='" + DocEntry + "' and T0.BaseType = '" + DocType + "' and T0.BaseEntry = '" + DocEntry + "' and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, isNull(T1.SysNumber, -1) SysNumber, isNull(T1.DistNumber, '') DistNumber, isNull(T1.InDate, '01/01/1900') InDate, isNull(T1.Notes, '') Notes, * from " + LineTableName1 + " left outer join SRI1_LINK T0  on T0.BaseEntry = " + LineTableName1 + ".DocEntry and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode" _
                            & " left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + "='" + DocEntry + "'" 'and T0.BaseType = '" + DocType + "' and T0.BaseEntry = '" + DocEntry + "' and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode"

                    Case "18"
                        'str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, T1.SysNumber, T1.DistNumber, T1.InDate, T1.Notes, * from " + LineTableName1 + ", SRI1_LINK T0 left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + "='" + DocEntry + "' and (T0.BaseType = '20' or T0.BaseType = '18' ) and T0.BaseEntry = '" + DocEntry + "' and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, T1.SysNumber, T1.DistNumber, T1.InDate, T1.Notes, * from " + LineTableName1 + " left outer join SRI1_LINK T0 on T0.BaseEntry = " + LineTableName1 + ".DocEntry and (T0.BaseType = 18 or T0.BaseType = 20) and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode" _
                        & " left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + " = '" + DocEntry + "' " 'and (T0.BaseType = '20' or T0.BaseType = '18' ) and T0.BaseEntry = '" + DocEntry + "' and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode"
                    Case "24"
                        KeyName = "DocNum"
                        If dbType = "VSM" Then
                            str1 = str1 + " select ROW_NUMBER() Over(Order By T2.Transid) No,T0.DocNum ,U_PACR,U_PACRDate,T0.InvType, T1.RefDate,T1.DueDate,'' BalDueDeb, T0.SumApplied,DATEDIFF(DD,T1.DueDate,T2.DocDate) Overdue,T0.DocLine "
                        Else
                            str1 = str1 + " select ROW_NUMBER() Over(Order By T2.Transid) No,T0.DocNum ,T0.InvType, T1.RefDate,T1.DueDate,'' BalDueDeb, T0.SumApplied,DATEDIFF(DD,T1.DueDate,T2.DocDate) Overdue,T0.DocLine "
                        End If
                        str1 = str1 + " from " + LineTableName1 + " T0"
                        str1 = str1 + " join JDT1 T1 on T0.InvType=case when TransType in ('24','46') then T1.ObjType else TransType end "
                        str1 = str1 + " and T0.DocEntry=case when TransType in ('24','46') then TransId else BaseRef end"
                        str1 = str1 + " join " + HeaderTableName + " T2 on T2.CardCode=T1.ShortName and T2.DocEntry=T0.DocNum"
                        str1 = str1 + " where T0.DocNum = " + DocEntry
                    Case "46"
                        KeyName = "DocNum"
                        str1 = str1 + " select ROW_NUMBER() Over(Order By T2.Transid) No,T0.DocNum ,T0.InvType, T1.RefDate,T1.DueDate,'' BalDueDeb, T0.SumApplied,DATEDIFF(DD,T1.DueDate,T2.DocDate) Overdue,T0.DocLine "
                        str1 = str1 + " from " + LineTableName1 + " T0"
                        str1 = str1 + " join JDT1 T1 on T0.InvType=case when TransType in ('24','46') then T1.ObjType else TransType end "
                        str1 = str1 + " and T0.DocEntry=case when TransType in ('24','46') then TransId else BaseRef end"
                        str1 = str1 + " join " + HeaderTableName + " T2 on T2.CardCode=T1.ShortName and T2.DocEntry=T0.DocNum"
                        str1 = str1 + " where T0.DocNum = " + DocEntry
                    Case "30"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,isnull(T1.AcctName,T2.CardName) Dscription, T0.* from " + LineTableName1 + " T0 "
                        str1 = str1 + " left join OACT T1 on T0.ShortName=T1.AcctCode"
                        str1 = str1 + " left join OCRD T2 on T0.ShortName= T2.CardCode"
                        str1 = str1 + " where " + KeyName + "='" + DocEntry + "'"
                    Case "202"
                        str1 = "Select ROW_NUMBER() Over(Order By T0." + KeyName + ") No,T1.ItemName Dscription,T0.* from " + LineTableName1
                        str1 = str1 + " T0 join OITM T1 on T0.ItemCode=T1.ItemCode where T0." + KeyName + "='" + DocEntry + "'"
                    Case Else
                        'If LineTableName1.ToUpper() = "FCT1" Then
                        '    str1 = "SELECT ROW_NUMBER() Over(Order By " + KeyName + ") No,T0.*,T1.ItemName,T1.U_ModelSales,T1.U_Color,T1.U_ProductSales FROM " + LineTableName1 + " T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE " + KeyName + "='" + DocEntry + "'"
                        'Else
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + LineTableName1 + " where " + KeyName + "='" + DocEntry + "'"
                        'End If
                End Select

                If LineTableName1.ToUpper() = "FCT1" Then
                    dt2 = connect.ObjectGetAll_Stored_SAP("sp_fun_GetFC", "@AbsID", DocEntry, dbType).Tables(0)
                    dt2.TableName = LineTableName1
                    ds.Tables.Add(dt2.Copy)
                Else
                    dt2 = connect.ObjectGetAll_Query(str1, dbType).Tables(0)
                    dt2.TableName = LineTableName1
                    ds.Tables.Add(dt2.Copy)
                End If

            End If

            If (LineTableName2 <> "") Then
                Dim str2 As String = ""
                Dim dt3 As New DataTable
                connect.setDB(dbType)
                Select Case DocType
                    Case "23" 'SALES QUOTATION --> Freight Charge
                        str2 = "select ROW_NUMBER() Over(Order By O.ExpnsCode) No, O.ExpnsCode, O.ExpnsName, Q.Status, Q.Comments, " _
                             + "IsNull(Q.LineTotal, 0) LineTotal, IsNull(Q.LineNum, O.ExpnsCode) LineNum, Q.DocEntry " _
                             + "from OEXD O left outer join " + LineTableName2 + " Q on O.ExpnsCode = Q.ExpnsCode and (Q." + KeyName + " = '" + DocEntry + "' Or '" + DocEntry + "' = '' ) "
                    Case Else
                        str2 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + LineTableName2 + " where " + KeyName + "='" + DocEntry + "'"
                End Select
                dt3 = connect.ObjectGetAll_Query(str2, dbType).Tables(0)
                dt3.TableName = LineTableName2
                ds.Tables.Add(dt3.Copy)
            End If
            Return ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Function GetMarketingDocument_OpenStatus(ByVal DocType As String, ByVal DocEntry As String, ByVal dbType As String) As DataSet
        Try
            Dim sStr As String = String.Empty
            Dim connect As New Connection()
            Dim HeaderTableName As String = String.Empty
            Dim LineTableName1 As String = String.Empty
            Dim LineTableName2 As String = String.Empty
            Dim KeyName As String = "DocEntry"
            Dim strFilterHdr = " "
            Dim strFilter = " and LineStatus = 'O' "

            Select Case DocType
                '----------------------PURCHASE------------------------
                Case "18" 'AP INVOICE
                    HeaderTableName = "OPCH"
                    LineTableName1 = "PCH1"

                Case "19" 'AP CREDIT
                    HeaderTableName = "ORPC"
                    LineTableName1 = "RPC1"
                Case "20" 'GOODS RECEIPT PO
                    HeaderTableName = "OPDN"
                    LineTableName1 = "PDN1"
                Case "21" 'GOODS RETURN
                    HeaderTableName = "ORPD"
                    LineTableName1 = "RPD1"
                Case "22" 'PURCHASE ORDER
                    HeaderTableName = "OPOR"
                    LineTableName1 = "POR1"
                Case "540000006" 'PURCHASE QUOTATION"
                    HeaderTableName = "OPQT"
                    LineTableName1 = "PQT1"
                Case "1470000113" 'PURCHASE REQUEST"
                    HeaderTableName = "OPRQ"
                    LineTableName1 = "PRQ1"
                    '-------------------SALES--------------------------
                Case "13" 'AR INVOICE
                    HeaderTableName = "OINV"
                    LineTableName1 = "INV1"
                Case "14" 'AR CREDIT
                    HeaderTableName = "ORIN"
                    LineTableName1 = "RIN1"
                Case "15" 'DELIVERY
                    HeaderTableName = "ODLN"
                    LineTableName1 = "DLN1"
                    'LineTableName2 = "DLN3"
                Case "16" 'RETURN
                    HeaderTableName = "ORDN"
                    LineTableName1 = "RDN1"
                Case "17" 'SALES ORDER
                    HeaderTableName = "ORDR"
                    LineTableName1 = "RDR1"
                    'LineTableName2 = "RDR3"
                Case "23" 'SALES QUOTATION
                    HeaderTableName = "OQUT"
                    LineTableName1 = "QUT1"
                    LineTableName2 = "QUT3"
                    '---------------------INVENTORY----------------------
                Case "59" 'GOODS RECEIPT
                    HeaderTableName = "OIGN"
                    LineTableName1 = "IGN1"
                Case "60" 'GOODS ISSUE
                    HeaderTableName = "OIGE"
                    LineTableName1 = "IGE1"
                Case "67" 'TRANSFER
                    HeaderTableName = "OWTR"
                    LineTableName1 = "WTR1"
                Case "1250000001" 'TRANSFER REQUEST
                    HeaderTableName = "OWTQ"
                    LineTableName1 = "WTQ1"
                    '-------------------MASTER DATA------------------
                Case "171" 'EMPLOYEE
                    HeaderTableName = "OHEM"
                    LineTableName1 = "HEM1"
                    strFilter = String.Empty ' no need filter by LineStatus
                    KeyName = "empID"
                Case "2" 'BUSINESS PARTNER
                    HeaderTableName = "OCRD"
                    LineTableName1 = "CRD1"
                    LineTableName2 = "OCPR"
                    KeyName = "cardcode"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "4" 'ITEM
                    HeaderTableName = "OITM"
                    LineTableName1 = ""
                    KeyName = "itemcode"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "11" 'CONTACT PERSON
                    HeaderTableName = "OCPR"
                    LineTableName1 = ""
                    KeyName = "cardcode"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "12" 'USER
                    HeaderTableName = "OUSR"
                    LineTableName1 = "OUSR"
                    KeyName = "USERID"
                    strFilter = String.Empty ' no need filter by LineStatus
                    '----------------------OTHERS--------------------
                Case "221" 'Attachment
                    HeaderTableName = "OATC"
                    LineTableName1 = "ATC1"
                    KeyName = "AbsEntry"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "97" 'Sales opportunity
                    HeaderTableName = "OOPR"
                    LineTableName1 = "OPR1"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "33" 'Activity
                    HeaderTableName = "OCLG"
                    LineTableName1 = ""
                    KeyName = "ClgCode"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "30" 'Journal Entry
                    HeaderTableName = "OJDT"
                    LineTableName1 = "JDT1"
                    KeyName = "TransID"
                    strFilter = String.Empty ' no need filter by LineStatus

                Case "112"
                    HeaderTableName = "ODRF"
                    LineTableName1 = "DRF1"
                    '-------------------PRODUCTION--------------------
                Case "202" 'Production Order
                    HeaderTableName = "OWOR"
                    LineTableName1 = "WOR1"
                    'Case "60-202" 'ISSUE for production
                    '    HeaderTableName = "OIGE"
                    '    LineTableName1 = "IGE1"
                    '    strFilter = " And BaseType='202'"
                    'Case "59-202" 'RECEIPT from production
                    '    HeaderTableName = "OIGN"
                    '    LineTableName1 = "IGN1"
                    '    strFilter = " And BaseType='202"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "198"
                    HeaderTableName = "OFCT"
                    LineTableName1 = "FCT1"
                    KeyName = "AbsID"
                    strFilter = String.Empty ' no need filter by LineStatus
                    '--------------------PAYMENT----------------------
                Case "24" 'Incoming
                    HeaderTableName = "ORCT"
                    LineTableName1 = "RCT2"
                    strFilter = String.Empty ' no need filter by LineStatus
                Case "46" 'Outgoing
                    HeaderTableName = "OVPM"
                    LineTableName1 = "VPM2"
                    strFilter = String.Empty ' no need filter by LineStatus
            End Select

            Dim ds As New DataSet("Document")
            Dim dt1 As New DataTable
            connect.setDB(dbType)
            Dim str As String
            str = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + HeaderTableName + " where " + KeyName + "='" + DocEntry + "' " + strFilterHdr

            dt1 = connect.ObjectGetAll_Query(str, dbType).Tables(0)

            dt1.TableName = HeaderTableName
            ds.Tables.Add(dt1.Copy)

            If LineTableName1 <> "" Then
                Dim dt2 As New DataTable
                connect.setDB(dbType)
                Dim str1 As String = ""

                Select Case DocType
                    Case "13", "67"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, isNull(T1.SysNumber, -1) SysNumber, isNull(T1.DistNumber, '') DistNumber, isNull(T1.InDate, '01/01/1900') InDate, isNull(T1.Notes, '') Notes, * from " + LineTableName1 + " left outer join SRI1_LINK T0  on T0.BaseEntry = " + LineTableName1 + ".DocEntry and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode" _
                            & " left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + "='" + DocEntry + "' " + strFilter

                    Case "18"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No, T1.SysNumber, T1.DistNumber, T1.InDate, T1.Notes, * from " + LineTableName1 + " left outer join SRI1_LINK T0 on T0.BaseEntry = " + LineTableName1 + ".DocEntry and (T0.BaseType = 18 or T0.BaseType = 20) and T0.ItemCode = " + LineTableName1 + ".ItemCode and T0.WhsCode = " + LineTableName1 + ".WhsCode" _
                        & " left join OSRN T1 on T1.SysNumber = T0.SysSerial and T1.ItemCode = T0.ItemCode  where " + KeyName + " = '" + DocEntry + "' " + strFilter

                    Case "24", "46"
                        KeyName = "DocNum"
                        str1 = str1 + " select ROW_NUMBER() Over(Order By T2.Transid) No,T0.DocNum ,T0.InvType, T1.RefDate,T1.DueDate,'' BalDueDeb, T0.SumApplied,DATEDIFF(DD,T1.DueDate,T2.DocDate) Overdue,T0.DocLine "
                        str1 = str1 + " from " + LineTableName1 + " T0"
                        str1 = str1 + " join JDT1 T1 on T0.InvType=case when TransType in ('24','46') then T1.ObjType else TransType end "
                        str1 = str1 + " and T0.DocEntry=case when TransType in ('24','46') then TransId else BaseRef end"
                        str1 = str1 + " join " + HeaderTableName + " T2 on T2.CardCode=T1.ShortName and T2.DocEntry=T0.DocNum"
                        str1 = str1 + " where T0.DocNum = " + DocEntry + strFilter
                    Case "30"
                        str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,isnull(T1.AcctName,T2.CardName) Dscription, T0.* from " + LineTableName1 + " T0 "
                        str1 = str1 + " left join OACT T1 on T0.ShortName=T1.AcctCode"
                        str1 = str1 + " left join OCRD T2 on T0.ShortName= T2.CardCode"
                        str1 = str1 + " where " + KeyName + "='" + DocEntry + "' " + strFilter
                    Case "202"
                        str1 = "Select ROW_NUMBER() Over(Order By T0." + KeyName + ") No,T1.ItemName Dscription,T0.* from " + LineTableName1
                        str1 = str1 + " T0 join OITM T1 on T0.ItemCode=T1.ItemCode where T0." + KeyName + "='" + DocEntry + "' " + strFilter
                    Case Else
                        If LineTableName1.ToUpper() = "FCT1" Then
                            str1 = "SELECT ROW_NUMBER() Over(Order By " + KeyName + ") No,T0.*,T1.ItemName,T1.U_ModelSales,T1.U_Color,T1.U_ProductSales FROM " + LineTableName1 + " T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE " + KeyName + "='" + DocEntry + "' " + strFilter
                        Else
                            str1 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + LineTableName1 + " where " + KeyName + "='" + DocEntry + "' " + strFilter
                        End If
                End Select

                dt2 = connect.ObjectGetAll_Query(str1, dbType).Tables(0)
                dt2.TableName = LineTableName1
                ds.Tables.Add(dt2.Copy)
            End If

            If (LineTableName2 <> "") Then
                Dim str2 As String = ""
                Dim dt3 As New DataTable
                connect.setDB(dbType)
                Select Case DocType
                    Case "23" 'SALES QUOTATION --> Freight Charge
                        str2 = "select ROW_NUMBER() Over(Order By O.ExpnsCode) No, O.ExpnsCode, O.ExpnsName, Q.Status, Q.Comments, " _
                             + "IsNull(Q.LineTotal, 0) LineTotal, IsNull(Q.LineNum, O.ExpnsCode) LineNum, Q.DocEntry " _
                             + "from OEXD O left outer join " + LineTableName2 + " Q on O.ExpnsCode = Q.ExpnsCode and (Q." + KeyName + " = '" + DocEntry + "' Or '" + DocEntry + "' = '' ) "
                    Case Else
                        str2 = "Select ROW_NUMBER() Over(Order By " + KeyName + ") No,* from " + LineTableName2 + " where " + KeyName + "='" + DocEntry + "'"
                End Select
                dt3 = connect.ObjectGetAll_Query(str2, dbType).Tables(0)
                dt3.TableName = LineTableName2
                ds.Tables.Add(dt3.Copy)
            End If
            Return ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Function CreateMarketingDocument(ByVal strXml As String, ByVal DocType As String, ByVal Key As String, _
                                            ByVal IsUpdate As Boolean, ByVal dbType As String) As DataSet
        Dim b As New SAP_Functions
        'Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Try
            Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            Select Case DocType
                Case "30"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.JournalEntries)
                Case "97"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.SalesOpportunities)
                Case "191"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.ServiceCalls)
                Case "33"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Contacts)
                Case "221"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Attachments2)
                Case "2"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.BusinessPartners)
                Case "28"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.IJournalVouchers)
                Case Else
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)
            End Select

            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            oCompany.XMLAsString = True
            oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0)
            If IsUpdate Then
                lErrCode = oDocment.GetByKey(Key)
                If oDocment.GetByKey(Key) Then
                    oDocment.Browser.ReadXML(strXml, 0)
                    lErrCode = oDocment.Update()
                Else
                    oCompany.GetLastError(lErrCode, sErrMsg)
                    Return b.ReturnMessage(-1, sErrMsg)
                End If
            Else
                lErrCode = oDocment.Add()
            End If
            If lErrCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrMsg)

                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                Dim lsDocEntry As String = "Operation successful!"
                If IsUpdate = False Then
                    lsDocEntry = oCompany.GetNewObjectKey()
                End If
                Return b.ReturnMessage(lErrCode, lsDocEntry)
            End If
        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CloseCancelDocument(ByVal DocType As String, ByVal Key As String, ByVal isCancel As Boolean, ByVal DbType As String) As DataSet
        Dim b As New SAP_Functions
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Try
            'Dim sStr As String = "Operation Completed successfuly!"
            'Dim oPO As SAPbobsCOM.Documents

            If DbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf DbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            Select Case DocType
                Case "30"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.JournalEntries)
                Case "97"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.SalesOpportunities)
                Case "191"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.ServiceCalls)
                Case "33"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Contacts)
                Case "221"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Attachments2)
                Case "2"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.BusinessPartners)
                Case "28"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.IJournalVouchers)

                Case "23"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations)
                Case "17"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders)
                Case "15"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes)
                Case "13"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)
                Case "24"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments)

                Case "22"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders)
                Case "20"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes)

                Case "67"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer)

                Case "112"
                    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts)

                Case Else
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)
            End Select

            'If isDraft = False Then
            '    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders)
            'Else
            '    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts)
            'End If

            'If isDraft = True Then
            '    oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts)
            'End If

            Dim errcode As Integer
            If oDocment.GetByKey(Key) Then
                If isCancel Then
                    errcode = oDocment.Cancel()
                Else
                    errcode = oDocment.Close()
                End If
                If errcode <> 0 Then
                    oCompany.GetLastError(errcode, sErrMsg)
                    Return b.ReturnMessage(lErrCode, sErrMsg)
                End If
            End If
            Return b.ReturnMessage(lErrCode, sStr)
        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    'Public Function UpdateXMLLog(ByVal UserCode As String, ByVal passWord As String, _
    '                                   ByVal Company As String, ByVal ObjType As String, _
    '                                   ByVal XML As String, ByVal ErrMessage As String, _
    '                                   ByVal DateTime As Date, ByVal isUpdate As String, ByVal key As String) As String
    '    Try
    '        Dim connect As New Connection()
    '        Dim oCompany As SAPbobsCOM.Company
    '        Dim errMsg As String = connect.setHoldingDB(oCompany)
    '        If errMsg.Length = 0 Then
    '            Dim params = New Object() {UserCode, passWord, Company, ObjType, XML, ErrMessage, DateTime, isUpdate, key}
    '            Dim query As String = String.Empty
    '            query = "INSERT INTO AB_XML_Log(UserCode,Password,Company,ObjType,XML,ErrMessage,PostTime,IsUpdate,DocEntry)"
    '            query += " VALUES(@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9)"
    '            Dim count As Integer = connect.Object_Execute_SAP(query, params)
    '            Return count
    '        End If
    '    Catch ex As Exception
    '        WriteLog(ex.StackTrace & ":" & ex.Message)
    '    End Try
    '    Return 0
    'End Function

    <WebMethod()> _
    Public Function UpdatePOCreateSO(ByVal strXmlPO As String, ByVal KeyPO As String, ByVal strXmlSO As String) As DataSet
        Dim b As New SAP_Functions
        'Dim connect As New Connection()
        Dim oCompanyPO As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim oCompanySO As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim lsDocEntry As String = "Operation successful!"

        Try
            'Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)

            ' Define companies (VSM, Dealer)
            oCompanyPO = PublicVariable.mDEADataComapny
            oCompanySO = PublicVariable.mVSMDataComapny

            ' ==================== Update PO ==================================
            oCompanyPO.XMLAsString = True
            oDocment = oCompanyPO.GetBusinessObjectFromXML(strXmlPO, 0)
            oCompanyPO.StartTransaction()

            If oDocment.GetByKey(KeyPO) Then
                oDocment.Browser.ReadXML(strXmlPO, 0)
                lErrCode = oDocment.Update()
            Else
                Return b.ReturnMessage(-1, "Record not found!")
            End If

            If lErrCode <> 0 Then
                If oCompanyPO.InTransaction Then
                    oCompanyPO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                oCompanyPO.GetLastError(lErrCode, sErrMsg)
                Return b.ReturnMessage(lErrCode, sErrMsg)
                'Else
                'Return b.ReturnMessage(lErrCode, "Operation successful!")
            End If
            ' ==================== End Update PO ==============================
            ' ==================== Create SO ==================================
            oCompanySO.XMLAsString = True
            oDocment = oCompanySO.GetBusinessObjectFromXML(strXmlSO, 0)
            oCompanySO.StartTransaction()

            lErrCode = oDocment.Add()

            If lErrCode <> 0 Then ' Get error --> RollBack PO and SO
                If oCompanyPO.InTransaction Then
                    oCompanyPO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                If oCompanySO.InTransaction Then
                    oCompanySO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If

                oCompanySO.GetLastError(lErrCode, sErrMsg)
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                lsDocEntry = oCompanySO.GetNewObjectKey()
            End If
            ' ==================== End Create SO ==============================

            ' Update PO & Created SO are already ==> Commit ===================
            If oCompanySO.InTransaction Then
                oCompanySO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
            End If
            If oCompanyPO.InTransaction Then
                oCompanyPO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
            End If
            Return b.ReturnMessage(lErrCode, lsDocEntry)

        Catch ex As Exception
            If oCompanyPO.InTransaction Then
                oCompanyPO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If

            If oCompanySO.InTransaction Then
                oCompanySO.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If

            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateAdvanvePayment(ByVal dbType As String, ByVal strXmlPayment As String, ByVal KeyPO As String, ByVal tblRDR1 As DataTable, ByVal strXmlCust As String, ByVal CardCode As String) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim lsDocEntry As String = "Operation successful!"

        Try
            'Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)

            ' ==================== Create Payment ==================================
            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            oCompany.XMLAsString = True
            oDocment = oCompany.GetBusinessObjectFromXML(strXmlPayment, 0)
            oCompany.StartTransaction()

            lErrCode = oDocment.Add()

            If lErrCode <> 0 Then ' Get error --> RollBack Payment

                oCompany.GetLastError(lErrCode, sErrMsg)

                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                lsDocEntry = oCompany.GetNewObjectKey()
            End If
            ' ==================== End Create Payment ==============================

            ' ==================== Update Customer Remain Amount ===================
            'oCompany.XMLAsString = True
            'oDocment = oCompany.GetBusinessObjectFromXML(strXmlCust, 0)

            'If oDocment.GetByKey(CardCode) Then
            '    oDocment.Browser.ReadXML(strXmlCust, 0)
            '    lErrCode = oDocment.Update()
            'Else
            '    If oCompany.InTransaction Then
            '        oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            '    End If
            '    Return b.ReturnMessage(-1, "Record not found!")
            'End If

            'If lErrCode <> 0 Then ' Get error --> RollBack Payment
            '    oCompany.GetLastError(lErrCode, sErrMsg)
            '    If oCompany.InTransaction Then
            '        oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            '    End If
            '    Return b.ReturnMessage(lErrCode, sErrMsg)
            'End If

            ' ==================== End Update Customer Remain Amount ===============

            ' ==================== Create Contract No ==============================
            Dim sqlList As ArrayList = New ArrayList()

            For Each row As DataRow In tblRDR1.Rows
                Dim KeyParameters As SQLHelper.DatabaseParameters = New SQLHelper.DatabaseParameters()

                KeyParameters.Add(New SQLHelper.DatabaseParameter("Selected", row("Selected").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("DocEntry", lsDocEntry))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("LineNum", row("LineNum").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("NumAtCard", row("NumAtCard").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_Color", row("U_Color").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_Model", row("U_Model").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_Product", row("U_Product").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("ItemCode", row("ItemCode").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("Dscription", row("Dscription").ToString(), True))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("Quantity", row("Quantity").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("PriceBefDi", row("PriceBefDi").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("DiscPrcnt", row("DiscPrcnt").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("Price", row("Price").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("LineTotal", row("LineTotal").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("ShipDate", row("ShipDate").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("Paid", row("Paid").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("Remain", row("Remain").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("CardCode", row("CardCode").ToString()))

                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_Discount", row("U_Discount").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_IncentiveDiscount", row("U_IncentiveDiscount").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("VatGroup", row("VatGroup").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("VatPrcnt", row("VatPrcnt").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("VatSum", row("VatSum").ToString()))
                KeyParameters.Add(New SQLHelper.DatabaseParameter("U_BaseLine", row("U_BaseLine").ToString()))

                sqlList.Add(SQLHelper.InsertCommand(KeyParameters, "tbl_RDR1"))

            Next

            ' ==================== End Create Contract No ==============================

            ' ==================== Commit Payment ======================================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                End If
            Else
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(-2, "Cannot save payment details. Error: " + PublicVariable.ErrMsg)
            End If

            Return b.ReturnMessage(lErrCode, lsDocEntry)

        Catch ex As Exception
            ' Rollback Payment
            If oCompany.InTransaction Then
                oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreatePO(ByVal dbType As String, ByVal strXml As String, ByVal IsUpdate As Boolean, ByVal KeyPO As String, ByVal tblFCPO As DataTable) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim lsDocEntry As String = "Operation successful!"

        Try
            'Dim sStr As String = "Operation Completed successfuly!"
            Dim oDocment
            oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)

            ' ==================== Create PO ==================================
            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            oCompany.XMLAsString = True
            oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0)
            oCompany.StartTransaction()

            If IsUpdate Then
                If oDocment.GetByKey(KeyPO) Then
                    oDocment.Browser.ReadXML(strXml, 0)
                    'oDocment.Lines.SetCurrentLine(0)
                    'oDocment.Lines.Delete()
                    'oDocment.Lines.SetCurrentLine(2)
                    'oDocment.Lines.Delete()
                    lErrCode = oDocment.Update()
                Else
                    Return b.ReturnMessage(-1, "Record not found!")
                End If
            Else
                lErrCode = oDocment.Add()
            End If

            If lErrCode <> 0 Then ' Get error --> RollBack PO
                oCompany.GetLastError(lErrCode, sErrMsg)
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                If IsUpdate Then
                    lsDocEntry = KeyPO
                Else
                    lsDocEntry = oCompany.GetNewObjectKey()
                End If
            End If
            ' ==================== End Create PO ==============================

            ' ==================== Create FC PO ==============================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each row As DataRow In tblFCPO.Rows
                KeyValues = New SQLHelper.DatabaseParameters()
                If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DocEntry", lsDocEntry))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Model", row("Model").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Product", row("Product").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty1", row("Qty1").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty2", row("Qty2").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty3", row("Qty1").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty4", row("Qty4").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DocDate", row("DocDate").ToString()))
                    sqlList.Add(SQLHelper.InsertCommand(KeyValues, "tbl_ForecastPO"))
                Else ' Update
                    KeyParameters = New SQLHelper.DatabaseParameters()
                    KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))

                    KeyValues.Add(New SQLHelper.DatabaseParameter("DocEntry", lsDocEntry))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Model", row("Model").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Product", row("Product").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty1", row("Qty1").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty2", row("Qty2").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty3", row("Qty1").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Qty4", row("Qty4").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DocDate", row("DocDate").ToString()))
                    sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, "tbl_ForecastPO"))
                End If

            Next

            ' ==================== End Create FC PO ==============================

            ' ==================== Commit PO ======================================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                End If
            Else
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(-2, "Cannot save payment details. Error: " + PublicVariable.ErrMsg)
            End If

            Return b.ReturnMessage(lErrCode, lsDocEntry)

        Catch ex As Exception
            ' Rollback PO
            If oCompany.InTransaction Then
                oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateUpdateCustomer(ByVal dbType As String, ByVal strXml As String, ByVal IsUpdate As Boolean, ByVal KeyCustomer As String, ByVal dsCRD10 As DataSet) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim lsDocEntry As String = "Operation successful!"

        Try
            Dim oDocment
            oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)

            ' ==================== Create/ Update Customer ====================
            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            oCompany.XMLAsString = True
            oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0)
            oCompany.StartTransaction()

            If IsUpdate Then
                If oDocment.GetByKey(KeyCustomer) Then
                    oDocment.Browser.ReadXML(strXml, 0)
                    lErrCode = oDocment.Update()
                Else
                    Return b.ReturnMessage(-1, "Record not found!")
                End If
            Else
                lErrCode = oDocment.Add()
            End If

            If lErrCode <> 0 Then ' Get error --> RollBack Customer
                oCompany.GetLastError(lErrCode, sErrMsg)
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                'If IsUpdate Then
                'lsDocEntry = KeyCustomer
                'lsDocEntry = oCompany.GetNewObjectKey()
                'Else
                lsDocEntry = oCompany.GetNewObjectKey()
                'End If
            End If
            ' ==================== End Create/ Update Customer ====================

            ' ==================== Create/ Update Customer Details ==============================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each tblCRD10 As DataTable In dsCRD10.Tables
                If tblCRD10.TableName = "tbl_DCRD10" Then
                    KeyValues = New SQLHelper.DatabaseParameters()
                    Dim dr As DataRow = tblCRD10.Rows(0)
                    If String.IsNullOrEmpty(dr("internal_id").ToString()) Or dr("internal_id").ToString() = "0" Then 'Add new
                        KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("CarType", dr("CarType").ToString()))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("PaymentMethod", dr("PaymentMethod").ToString(), True))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("BankName", dr("BankName").ToString(), True))
                        sqlList.Add(SQLHelper.InsertCommand(KeyValues, tblCRD10.TableName))
                    Else ' Update
                        KeyParameters = New SQLHelper.DatabaseParameters()
                        KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", dr("internal_id").ToString()))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("CarType", dr("CarType").ToString()))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("PaymentMethod", dr("PaymentMethod").ToString(), True))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("BankName", dr("BankName").ToString(), True))
                        KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                        sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, tblCRD10.TableName))
                    End If
                Else ' table tbl_ACRD10, tbl_BCRD10, tbl_CCRD10, tbl_ECRD10, tbl_FCRD10
                    For Each row As DataRow In tblCRD10.Rows
                        KeyValues = New SQLHelper.DatabaseParameters()
                        If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Code", row("Code").ToString()))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Name", row("Name").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Selected", row("Selected").ToString()))
                            sqlList.Add(SQLHelper.InsertCommand(KeyValues, tblCRD10.TableName))
                        Else ' Update
                            KeyParameters = New SQLHelper.DatabaseParameters()
                            KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Code", row("Code").ToString()))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Name", row("Name").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Selected", row("Selected").ToString()))
                            sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, tblCRD10.TableName))
                        End If
                    Next
                End If
            Next

            ' ==================== End Create/ Update ...CRD10 tables ==============================

            ' ==================== Commit Customer =================================================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                End If
            Else
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(-2, "Cannot save Business Partner. Error: " + PublicVariable.ErrMsg)
            End If

            Return b.ReturnMessage(lErrCode, lsDocEntry)

        Catch ex As Exception
            ' Rollback Customer
            If oCompany.InTransaction Then
                oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateUpdateDealer(ByVal dbType As String, ByVal strXml As String, ByVal IsUpdate As Boolean, ByVal KeyDealer As String, ByVal dsCRDDetails As DataSet) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()
        Dim oCompany As SAPbobsCOM.Company = New SAPbobsCOM.Company
        Dim lsDocEntry As String = "Operation successful!"

        Try
            Dim oDocment
            oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)

            ' ==================== Create/ Update Dealer ====================
            If dbType = PublicVariable.VSM Then
                oCompany = PublicVariable.mVSMDataComapny
            ElseIf dbType = PublicVariable.DEALER Then
                oCompany = PublicVariable.mDEADataComapny
            End If

            oCompany.XMLAsString = True
            oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0)
            oCompany.StartTransaction()

            If IsUpdate Then
                If oDocment.GetByKey(KeyDealer) Then
                    oDocment.Browser.ReadXML(strXml, 0)
                    lErrCode = oDocment.Update()
                Else
                    Return b.ReturnMessage(-1, "Record not found!")
                End If
            Else
                lErrCode = oDocment.Add()
            End If

            If lErrCode <> 0 Then ' Get error --> RollBack Customer
                oCompany.GetLastError(lErrCode, sErrMsg)
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(lErrCode, sErrMsg)
            Else
                'If IsUpdate Then
                'lsDocEntry = KeyCustomer
                'lsDocEntry = oCompany.GetNewObjectKey()
                'Else
                lsDocEntry = oCompany.GetNewObjectKey()
                'End If
            End If
            ' ==================== End Create/ Update Dealer ====================

            ' ==================== Create/ Update Dealer Details ==============================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each tblCRDDetails As DataTable In dsCRDDetails.Tables
                If tblCRDDetails.TableName = "tbl_ShowRoom" Then ' Show Room table
                    For Each row As DataRow In tblCRDDetails.Rows
                        KeyValues = New SQLHelper.DatabaseParameters()
                        If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ShowRoomName", row("ShowRoomName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Address", row("Address").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("TelFax", row("TelFax").ToString(), True))

                            KeyValues.Add(New SQLHelper.DatabaseParameter("ShowRoomArea", row("ShowRoomArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardArea", row("ServiceYardArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GeneralArea", row("GeneralArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GroundRent", row("GroundRent").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("OwnerInfo", row("OwnerInfo").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("LandLeasingPeriod", row("LandLeasingPeriod").ToString(), True))
                            sqlList.Add(SQLHelper.InsertCommand(KeyValues, tblCRDDetails.TableName))
                        Else ' Update
                            KeyParameters = New SQLHelper.DatabaseParameters()
                            KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString())) ' Condition to upate
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ShowRoomName", row("ShowRoomName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Address", row("Address").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("TelFax", row("TelFax").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ShowRoomArea", row("ShowRoomArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardArea", row("ServiceYardArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GeneralArea", row("GeneralArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GroundRent", row("GroundRent").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("OwnerInfo", row("OwnerInfo").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("LandLeasingPeriod", row("LandLeasingPeriod").ToString(), True))
                            sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, tblCRDDetails.TableName))
                        End If
                    Next

                ElseIf tblCRDDetails.TableName = "tbl_ServiceYard" Then ' Service Yard table
                    For Each row As DataRow In tblCRDDetails.Rows
                        KeyValues = New SQLHelper.DatabaseParameters()
                        If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardName", row("ServiceYardName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Address", row("Address").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("TelFax", row("TelFax").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardArea4VSM", row("ServiceYardArea4VSM").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GeneralArea", row("GeneralArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GroundRent", row("GroundRent").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("OwnerInfo", row("OwnerInfo").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("LandLeasingPeriod", row("LandLeasingPeriod").ToString(), True))
                            sqlList.Add(SQLHelper.InsertCommand(KeyValues, tblCRDDetails.TableName))
                        Else ' Update
                            KeyParameters = New SQLHelper.DatabaseParameters()
                            KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString())) ' condition to update
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardName", row("ServiceYardName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Address", row("Address").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("TelFax", row("TelFax").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("ServiceYardArea4VSM", row("ServiceYardArea4VSM").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GeneralArea", row("GeneralArea").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GroundRent", row("GroundRent").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("OwnerInfo", row("OwnerInfo").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("LandLeasingPeriod", row("LandLeasingPeriod").ToString(), True))
                            sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, tblCRDDetails.TableName))
                        End If
                    Next

                ElseIf tblCRDDetails.TableName = "tbl_GroupCompany" Then ' Group Companies table
                    For Each row As DataRow In tblCRDDetails.Rows
                        KeyValues = New SQLHelper.DatabaseParameters()
                        If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GrpName", row("GrpName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Place", row("Place").ToString(), True))
                            sqlList.Add(SQLHelper.InsertCommand(KeyValues, tblCRDDetails.TableName))
                        Else ' Update
                            KeyParameters = New SQLHelper.DatabaseParameters()
                            KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("GrpName", row("GrpName").ToString(), True))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", lsDocEntry))
                            KeyValues.Add(New SQLHelper.DatabaseParameter("Place", row("Place").ToString(), True))
                            sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, tblCRDDetails.TableName))
                        End If
                    Next
                End If

            Next

            ' ==================== End Create/ Update ...CRDDetails tables ==============================

            ' ==================== Commit Customer =================================================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                End If
            Else
                If oCompany.InTransaction Then
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                End If
                Return b.ReturnMessage(-2, "Cannot save Business Partner. Error: " + PublicVariable.ErrMsg)
            End If

            Return b.ReturnMessage(lErrCode, lsDocEntry)

        Catch ex As Exception
            ' Rollback Customer
            If oCompany.InTransaction Then
                oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
            End If
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateEmpTarget(ByVal dbType As String, ByVal tblEmpTarget As DataTable) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()

        Try
            ' ==================== Create Employee Target ==============================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each row As DataRow In tblEmpTarget.Rows
                KeyValues = New SQLHelper.DatabaseParameters()
                If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                    KeyValues.Add(New SQLHelper.DatabaseParameter("SlpCode", row("SlpCode").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DateTarget", row("DateTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("QtyTarget", row("QtyTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("ID_Dealer", row("ID_Dealer").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Comments", row("Comments").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("AmountTarget", "0"))
                    sqlList.Add(SQLHelper.InsertCommand(KeyValues, "tbl_EmpTarget"))
                Else ' Update
                    KeyParameters = New SQLHelper.DatabaseParameters()
                    KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))

                    KeyValues.Add(New SQLHelper.DatabaseParameter("SlpCode", row("SlpCode").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DateTarget", row("DateTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("QtyTarget", row("QtyTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("ID_Dealer", row("ID_Dealer").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Comments", row("Comments").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("AmountTarget", "0"))
                    sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, "tbl_EmpTarget"))
                End If

            Next
            ' ==================== End Create Employee Target ======================
            ' ==================== Commit Employee Target ===========================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                Return b.ReturnMessage(0, "Operation successful!")
            Else
                Return b.ReturnMessage(-2, "Can not create Employees Target.")
            End If

        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateDealerTarget(ByVal dbType As String, ByVal tblDealerTarget As DataTable) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()

        Try
            ' ==================== Create Dealer Target ==============================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each row As DataRow In tblDealerTarget.Rows
                KeyValues = New SQLHelper.DatabaseParameters()
                If String.IsNullOrEmpty(row("internal_id").ToString()) Or row("internal_id").ToString() = "0" Then 'Add new
                    KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", row("CardCode").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Model", row("Model").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Product", row("Product").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DateTarget", row("DateTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("QtyTarget", row("QtyTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Comments", row("Comments").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("AmountTarget", "0"))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("PlanType", row("PlanType").ToString()))
                    sqlList.Add(SQLHelper.InsertCommand(KeyValues, "tbl_DealerTarget"))
                Else ' Update
                    KeyParameters = New SQLHelper.DatabaseParameters()
                    KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))

                    KeyValues.Add(New SQLHelper.DatabaseParameter("CardCode", row("CardCode").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Model", row("Model").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Product", row("Product").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("DateTarget", row("DateTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("QtyTarget", row("QtyTarget").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("Comments", row("Comments").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("AmountTarget", "0"))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("PlanType", row("PlanType").ToString()))
                    sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, "tbl_DealerTarget"))
                End If

            Next
            ' ==================== End Create Dealer Target ======================
            ' ==================== Commit Dealer Target ===========================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                Return b.ReturnMessage(0, "Operation successful!")
            Else
                Return b.ReturnMessage(-2, "Can not create Sales Plan.")
            End If

        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function CreateMasterFile(ByVal dbType As String, ByVal tblMasterFile As DataTable, ByVal isUpdate As Boolean) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()

        Try
            ' ==================== Create Document Profile ==========================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyValues As SQLHelper.DatabaseParameters = Nothing, KeyParameters As SQLHelper.DatabaseParameters = Nothing
            For Each row As DataRow In tblMasterFile.Rows
                KeyValues = New SQLHelper.DatabaseParameters()
                KeyParameters = New SQLHelper.DatabaseParameters()
                If (isUpdate = False) Then
                    KeyValues.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("title", row("title").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("publish_date", row("publish_date").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("valid_date", row("valid_date").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("isinvalid", row("isinvalid").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("document_type", row("document_type").ToString()))

                    KeyValues.Add(New SQLHelper.DatabaseParameter("dept", row("dept").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("file_name", row("file_name").ToString(), True))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("file_type", row("file_type").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("file_date", row("file_date").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("file_by", row("file_by").ToString()))
                    If (row("ispublic").ToString() = "0") Then
                        KeyValues.Add(New SQLHelper.DatabaseParameter("ispublic", "False"))
                    Else
                        KeyValues.Add(New SQLHelper.DatabaseParameter("ispublic", "True"))
                    End If
                    sqlList.Add(SQLHelper.InsertCommand(KeyValues, "tbl_masterfile"))
                Else
                    KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", row("internal_id").ToString()))
                    KeyValues.Add(New SQLHelper.DatabaseParameter("isinvalid", row("isinvalid").ToString()))
                    sqlList.Add(SQLHelper.UpdateCommand(KeyParameters, KeyValues, "tbl_masterfile"))
                End If
            Next
            ' ==================== End Create Document Profile ======================
            ' ==================== Commit Create Document Profile ===================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                Return b.ReturnMessage(0, "Operation successful!")
            Else
                Return b.ReturnMessage(-2, "Can not create Document Profile.")
            End If

        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function DeleteMasterFile(ByVal dbType As String, ByVal InternalID As String) As DataSet
        Dim b As New SAP_Functions
        Dim connect As New Connection()

        Try
            ' ==================== Delete Document Profile ==========================
            Dim sqlList As ArrayList = New ArrayList()
            Dim KeyParameters As SQLHelper.DatabaseParameters = Nothing
            KeyParameters = New SQLHelper.DatabaseParameters()
            KeyParameters.Add(New SQLHelper.DatabaseParameter("internal_id", InternalID))

            sqlList.Add(SQLHelper.DeleteCommand(KeyParameters, "tbl_masterfile"))
            ' ==================== End Delete Document Profile ======================
            ' ==================== Commit Delete Document Profile ===================
            connect.setDB(dbType)
            If connect.ExecuteQuery(sqlList, dbType) Then
                Return b.ReturnMessage(0, "Operation successful!")
            Else
                Return b.ReturnMessage(-2, "Can not Delete Document Profile.")
            End If

        Catch ex As Exception
            Return b.ReturnMessage(-1, ex.ToString)
        End Try
        Return Nothing
    End Function

    <WebMethod()> _
    Public Function GetDocumentForPayment(ByVal dbType As String, ByVal ID_Dealer As String, ByVal CardCode As String, ByVal DocDate As Date) As DataSet
        Try
            Dim connect As New Connection()
            Dim ds As New DataSet("OCRD")
            Dim str As String
            str = "select ROW_NUMBER() Over(Order By Transid) No, "
            str = str + " case when TransType in ('24','46') then TransId else BaseRef end DocNum,"
            str = str + " case when TransType in ('24','46') then T0.ObjType else TransType end InvType, RefDate, DueDate,"
            str = str + " DATEDIFF(dd,duedate,'" + DocDate.ToString("MM/dd/yyyy") + "') Overdue, Line_ID DocLine,"
            str = str + " case when T1.CardType='S' then T0.BalDueCred-T0.BalDueDeb else T0.BalDueDeb-T0.BalDueCred  End BalDueDeb, "
            str = str + " case when T1.CardType='S' then T0.BalDueCred-T0.BalDueDeb else T0.BalDueDeb-T0.BalDueCred  End SumApplied"
            str = str + " from jdt1 T0 "
            str = str + " join ocrd T1 on cardcode=shortname"
            str = str + " where shortname='" + CardCode + "' "
            str = str + " and (BalDueDeb<>0 or BalDueCred<>0)" ' and DATEDIFF(dd, DueDate,'" + DocDate.ToString("MM/dd/yyyy") + "')>=0
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetEmpTarget(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ID_Emp As String, ByVal Year As String) As DataSet
        Try
            Dim connect As New Connection()
            Dim ds As New DataSet("tbl_EmpTarget")
            Dim str As String
            str = "select O.SlpCode, case when O.U_Certificate = 'Y' then 1 else 0 end as U_Certificate, "
            str += " isNull(T.DateTarget, GETDATE()) DateTarget, T.QtyTarget, T.AmountTarget, O.U_Code As ID_Dealer, Comments, T.internal_id, O.SlpName, O.U_Date, 0 Total " 'GETDATE() As U_Date
            str += " from dbo.tbl_EmpTarget T right outer join OSLP O on T.SlpCode = O.SlpCode and T.ID_Dealer = O.U_Code and (Year(T.DateTarget) = " + Year + " or T.DateTarget is null ) "
            str += " where ( O.U_Code = '" + ID_Dealer + "' or '" + ID_Dealer + "' = '') "
            str += " and ( O.SlpCode = '" + ID_Emp + "' or '" + ID_Emp + "' = '')  and O.Active = 'Y' and O.U_Date is not null "

            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function UpdateSerial(ByVal AbsEntry As Integer, ByVal MfDate As Date) As String
        Try
            Dim companyService As SAPbobsCOM.CompanyService
            companyService = PublicVariable.mVSMDataComapny.GetCompanyService

            Dim SerialService As SAPbobsCOM.SerialNumberDetailsService
            SerialService = companyService.GetBusinessService(SAPbobsCOM.ServiceTypes.SerialNumberDetailsService)
            Dim SerialDetail As SAPbobsCOM.SerialNumberDetail
            SerialDetail = SerialService.GetDataInterface(SAPbobsCOM.SerialNumberDetailsServiceDataInterfaces.sndsSerialNumberDetail)
            Dim SerialPara As SAPbobsCOM.SerialNumberDetailParams
            SerialPara = SerialService.GetDataInterface(SAPbobsCOM.SerialNumberDetailsServiceDataInterfaces.sndsSerialNumberDetailParams)

            SerialPara.DocEntry = AbsEntry

            SerialDetail = SerialService.Get(SerialPara)
            SerialDetail.ManufacturingDate = MfDate
            SerialService.Update(SerialDetail)

            Return String.Empty
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    <WebMethod()> _
    Public Function GetDealerTarget(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Year As String, ByVal PlanType As String) As DataSet
        Try
            Dim connect As New Connection()
            Dim ds As New DataSet("tbl_DealerTarget")
            Dim str As String
            'str = "select * from tbl_DealerTarget where Year(DateTarget) = " + Year + " and ( CardCode = '" + ID_Dealer + "' or '" + ID_Dealer + "' = '') "
            str = "select distinct D.CardCode, O.U_ModelSales As Model, O.U_ProductSales As Product, isNull(D.DateTarget, GetDate()) As DateTarget, isNull(D.QtyTarget, 0) As QtyTarget, isNull(D.AmountTarget, 0) As AmountTarget, D.Comments, D.internal_id, D.PlanType, 0 Total "
            str += "from tbl_DealerTarget D right outer join OITM O on D.Model = O.U_ModelSales and D.Product = O.U_ProductSales and Year(D.DateTarget) = " + Year + " and ( D.CardCode = '" + ID_Dealer + "' or '" + ID_Dealer + "' = '') and ( D.PlanType is null Or D.PlanType = " + PlanType + " ) "
            str += "where O.ItmsGrpCod in ('101', '103') and O.U_ModelSales is not null and O.U_ProductSales is not null and (O.frozenFor <> 'Y' and O.validFor <> 'N') "
            connect.setDB(dbType)
            ds = connect.ObjectGetAll_Query(str, dbType)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class